var express = require('express');
var Msdb = require("../../../db/Msdb.js");
var router = express.Router();   //可使用 express.Router 类创建模块化、可挂载的路由句柄
const jwt = require('jsonwebtoken');
var Excel = require('exceljs');
var multer = require('multer');
var UUID = require('uuid');
var request = require("request");
const { getxl_util, getnowdate, saveczlog_util, res_util, checkbysql, addformfield, getnowdate_djh, setformfield,
   insert_table, update_table_wstr, update_table_byid, resutdata_to_formdata, resutdata_to_formdata_arr, save_qlczslog_util,
   usequery,
   getxl_util_free } = require('./server_util.js')

const log = require('../../../logger.js');
const db = require('../../../db/Redisdb.js');
const { use_sltzd_to_cgdd, use_cgrkd_to_cgdd, use_qdgmx_qgd } = require('./ddd_util.js');
var logger = require('../../../logger.js').logger;


//查询采购订单列表
router.post('/cgdd_list', async function (req, res) {
   let { type, startDataStrref, endDataStrref } = req.body
   var querysql = ``
   if (type == 'mx') {
      querysql = `
      SELECT TOP 1000 a.id,a.gys gysq,a.create_by create_byq,
      a.create_time create_timeq,
      a.djh ydh,a.cgfw,a.jsfs,a.djh AS djhyl,a.gysid,a.zsl,a.zslsl,a.zrksl,
      a.djzt,a.bb,a.bm,a.zdr,a.shr,a.zg,a.ywy,
      CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
          THEN a.djh ELSE NULL END AS djh,
      CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
          THEN a.rq ELSE NULL END AS rq,
      CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
          THEN a.create_by ELSE NULL END AS create_by,
      CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
          THEN a.gys ELSE NULL END AS gys,
       b.wlmc, b.wlbm, b.ggxh, b.bbh,b.sl,b.id AS mxid,b.rksl,b.dw,b.slsl,b.xtzt,b.sysl,b.jdbm
      FROM shipSRM_cgdd a
      INNER JOIN shipSRM_cgdd_mx b ON b.mainid = a.id where 1=1 and a.djlx is null 
      `
   } else {
      querysql = `select TOP 1000 * from shipSRM_cgdd where 1=1 and djlx is null `
   }

   if (startDataStrref || endDataStrref) {
      querysql = querysql.replace('TOP 1000', '')
      if (type === "mx") {
         if (startDataStrref && endDataStrref) {
            querysql += ` AND a.create_time BETWEEN '${startDataStrref}' AND '${endDataStrref} 23:59:59'`;
         } else if (startDataStrref) {
            querysql += ` AND a.create_time > '${startDataStrref}' `;
         } else if (endDataStrref) {
            querysql += ` AND a.create_time < '${endDataStrref} 23:59:59' `;
         }
      } else {
         if (startDataStrref && endDataStrref) {
            querysql += ` AND create_time BETWEEN '${startDataStrref}' AND '${endDataStrref} 23:59:59' `;
         } else if (startDataStrref) {
            querysql += ` AND create_time > '${startDataStrref}' `;
         } else if (endDataStrref) {
            querysql += ` AND create_time < '${endDataStrref} 23:59:59' `;
         }
      }
   }


   if (type === "mx") {
      querysql += ` ORDER BY a.djzt desc,a.rq desc`
   } else {
      querysql += ` ORDER BY rq desc `
   }

   var rtdata = await usequery(querysql)
   res_util(res, rtdata, true, "查询成功")
})



//查询采购订单列表通过id
router.post('/query_cgdd_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *,CONVERT(varchar(100), create_time, 23) as djrq from shipSRM_cgdd where id ='${id}'`)
   var mxdata = await usequery(`select a.*,CONVERT(varchar(100), b.create_time, 23) as djrq  from shipSRM_cgdd_mx a,shipSRM_cgdd b where mainid ='${id}' and a.mainid = b.id`)
   res_util(res, { formdata: formdata[0], mxdata }, true, "查询成功")
})

//新增采购订单
router.post('/add_cgdd', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util_free("seq_cgdd")
   //创建序列sql server  1000000000开始 seq_cgdd

   formdata.djh.value = "CGDD" + xlm
   formdata.djzt.value = "正常"
   var insrtobj = await insert_table(formdata, "shipSRM_cgdd")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增采购订单', '新增', 'shipSRM_cgdd')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_cgdd_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增采购订单明细', '新增', 'shipSRM_cgdd_mx')
   }

   await use_sltzd_to_cgdd()
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_cgdd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增采购订单成功，单据号为${formdata.djh.value}`)
})

//更新采购订单
router.post('/update_cgdd', async function (req, res) {
   let db = new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_cgdd")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改采购订单', '修改', 'shipSRM_cgdd')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_cgdd_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_cgdd_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_cgdd_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_cgdd_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_cgdd_mx where mainid ='${formdata.id.value}'`)

   await use_sltzd_to_cgdd()
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新采购订单成功")
})


//  删除采购订单
router.post('/delete_cgdd', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db = new Msdb()
   var ifcz = await usequery(`select * from shipSRM_sltzd_mx where ydid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   if (ifcz.length > 0) {
      res_util(res, {}, false, "删除失败，存在关联数据")
      return
   }
   const delsql = `delete from shipSRM_cgdd where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_cgdd_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除采购订单', '删除', 'shipSRM_cgdd')
   res_util(res, {}, true, "删除成功")
})

//  采购订单明细是否关联收料通知单
router.post('/delete_cgdd_mx', async function (req, res) {
   let { selectedRows, storage } = req.body
   var ifcz = await usequery(`select * from shipSRM_sltzd_mx where ydmxid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   if (ifcz.length > 0) {
      res_util(res, {}, false, "删除失败，该明细行存在关联数据")
      return
   }
   res_util(res, {}, true, "")
})

//  收料通知单明细是否关联采购入库单明细
router.post('/delete_sltzd_mx', async function (req, res) {
   let { selectedRows, storage } = req.body
   var ifcz = await usequery(`select * from shipSRM_cgrk_detail where ydmxid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   if (ifcz.length > 0) {
      res_util(res, {}, false, "删除失败，该明细行存在关联数据")
      return
   }
   res_util(res, {}, true, "")
})

//查询采购入库单列表
router.post('/cgrkd_list', async function (req, res) {
   let { startDataStrref, endDataStrref, type } = req.body
   var querysql = ``
   type == 'mx' ? querysql = `
   SELECT top 1000 a.ydbh,a.ydid,a.id,a.create_by create_byq,a.gysmc gysq,a.cgy cgyq,a.rkrq,a.bgr, a.djh ydh,a.pjh,a.bz,
   CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
       THEN a.djh ELSE NULL END AS djh,
   CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
       THEN a.create_by ELSE NULL END AS create_by,
   CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
       THEN a.create_time ELSE NULL END AS create_time,
   CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
       THEN a.gysmc ELSE NULL END AS gysmc,
    b.wlmc, b.wlbm, b.ggxh, b.sl,b.slsl,b.bbh,b.id AS mxid,b.jdbm
   FROM shipSRM_cgrk a
   INNER JOIN shipSRM_cgrk_detail b ON b.mainid = a.id
   where 1=1
 `: querysql = `select top 1000 * from shipSRM_cgrk where 1=1`


   if (type === "mx") {
      if (startDataStrref && endDataStrref) {
         querysql = querysql.replace('top 1000', '')
         querysql += ` AND a.create_time BETWEEN '${startDataStrref}' AND '${endDataStrref} 23:59:59' `;
      } else if (startDataStrref) {
         querysql = querysql.replace('top 1000', '')
         querysql += ` AND a.create_time > '${startDataStrref}' `;
      } else if (endDataStrref) {
         querysql = querysql.replace('top 1000', '')
         querysql += ` AND a.create_time < '${endDataStrref} 23:59:59' `;
      }
      querysql += ` ORDER BY a.rkrq DESC, b.id `
   } else {
      if (startDataStrref && endDataStrref) {
         querysql = querysql.replace('top 1000', '')
         querysql += ` AND create_time BETWEEN '${startDataStrref}' AND '${endDataStrref} 23:59:59' `;
      } else if (startDataStrref) {
         querysql = querysql.replace('top 1000', '')
         querysql += ` AND create_time > '${startDataStrref}' `;
      } else if (endDataStrref) {
         querysql = querysql.replace('top 1000', '')
         querysql += ` AND create_time < '${endDataStrref} 23:59:59' `;
      }
      querysql += ` ORDER BY create_time desc `
   }
   var rtdata = await usequery(querysql)
   res_util(res, rtdata, true, "查询成功")
})

//查询采购入库单列表通过id
router.post('/query_cgrkd_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_cgrk where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_cgrk_detail where mainid ='${id}'`)
   res_util(res, { formdata: formdata[0], mxdata }, true, "查询成功")
})

//新增采购入库单
router.post('/add_cgrkd', async function (req, res) {
   var db = new Msdb()
   let { formdata, mxdata, storage, type } = req.body
   let xlm = await getxl_util_free("seq_cgrkd")
   formdata.djh.value = "CGRKD" + xlm
   var insrtobj = await insert_table(formdata, "shipSRM_cgrk")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增采购入库单', '新增', 'shipSRM_cgrk')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_cgrk_detail")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增采购入库单明细', '新增', 'shipSRM_cgrk_detail')
      await db.update(`update shipSRM_sltzd_mx set xtzt='已下推' where id = '${mxitem.ydmxid.value}'`)
      if (type == "mjxt") {
         await db.update(`update shipSRM_sltzd_mx set xtlx='免检下推' where id = '${mxitem.ydmxid.value}'`)
      }
   }
   await use_cgrkd_to_cgdd()
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_cgrk_detail where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增采购入库单成功，单据号为{${formdata.djh.value}`)
})

//更新采购入库单
router.post('/update_cgrkd', async function (req, res) {
   let db = new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_cgrk")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改采购入库单', '修改', 'shipSRM_cgrk')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_cgrk_detail where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_cgrk_detail")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_cgrk_detail")
      }
   }
   //删除delidarr
   await db.update(`delete from shipSRM_cgrk_detail where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   await use_cgrkd_to_cgdd()

   var rtmxdata = await usequery(`select *  from shipSRM_cgrk_detail where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新采购入库单成功")
})

//  删除采购入库单
router.post('/delete_cgrkd', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db = new Msdb()
   const delsql = `delete from shipSRM_cgrk where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_cgrk_detail where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除采购入库单', '删除', 'shipSRM_cgrk')
   await use_cgrkd_to_cgdd()
   res_util(res, {}, true, "删除成功")
})

//查询收料通知单列表
router.post('/sltzd_list', async function (req, res) {
   let { type, startDataStrref, endDataStrref } = req.body
   let querysql = type === 'mx' ? `
      SELECT TOP 1000 a.id,a.create_by create_byq,a.gysmc gysq,a.cgymc,a.djrq,a.djh ydh,a.gysbm,a.cgybm,djzt,
      CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
          THEN a.djh ELSE NULL END AS djh,
      CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
          THEN a.create_by ELSE NULL END AS create_by,
      CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
          THEN a.create_time ELSE NULL END AS create_time,
      CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
          THEN a.gysmc ELSE NULL END AS gysmc,
      b.jdbm, b.wlmc, b.wlbm, b.ggxh, b.dw,b.cgsl,b.slsl,b.rksl,b.sysl,b.bbh,b.xtzt,b.sfllj,b.id AS mxid,b.jys,b.bz,
			 b.jyr,b.jysj,b.bls,b.ydbh
      FROM shipSRM_sltzd a
      INNER JOIN shipSRM_sltzd_mx b ON b.mainid = a.id
      where 1=1
    ` : `select TOP 1000 * from shipSRM_sltzd where 1=1`

   if (startDataStrref && endDataStrref) {
      querysql = querysql.replace('TOP 1000', '')
      querysql += ` AND ${type === 'mx' ? 'a.' : ''}create_time BETWEEN '${startDataStrref}' AND '${endDataStrref} 23:59:59' `;
   } else if (startDataStrref) {
      querysql = querysql.replace('TOP 1000', '')
      querysql += ` AND ${type === 'mx' ? 'a.' : ''}create_time > '${startDataStrref}' `;
   } else if (endDataStrref) {
      querysql = querysql.replace('TOP 1000', '')
      querysql += ` AND ${type === 'mx' ? 'a.' : ''}create_time < '${endDataStrref} 23:59:59' `;
   }
   querysql += ` ORDER BY ${type === 'mx' ?
      'a.djzt DESC,a.create_time DESC' : 'a.create_time desc'}`
   var rtdata = await usequery(querysql)
   res_util(res, rtdata, true, "查询成功")
})

//查询未检验收料通知单列表
router.post('/sltzd_list_mobile_wj', async function (req, res) {
   let { keyword, startDataStrref, endDataStrref } = req.body
   let querysql = `
SELECT a.*,b.djrq,b.gysmc,b.djh FROM 
(select * from shipSRM_sltzd_mx  where 
   id not in (select ydmxid from shipSRM_cgrk_detail where ydmxid is not null)
	 and sfllj is null 
) a
LEFT JOIN shipSRM_sltzd b ON a.mainid = b.id where b.create_time >'2024-10-24'
    `
   if (keyword) {
      querysql += ` and (a.wlmc like '%${keyword}%' or a.ggxh like '%${keyword}%' or b.djh like '%${keyword}%')`
   }
   // if (startDataStrref && endDataStrref) {
   //    querysql = querysql.replace('TOP 1000', '')
   //    querysql += ` AND  create_time BETWEEN '${startDataStrref}' AND '${endDataStrref}' `;
   // } else if (startDataStrref) {
   //    querysql = querysql.replace('TOP 1000', '')
   //    querysql += ` AND  create_time > '${startDataStrref}' `;
   // } else if (endDataStrref) {
   //    querysql = querysql.replace('TOP 1000', '')
   //    querysql += ` AND  create_time < '${endDataStrref}' `;
   // }
   querysql += ` ORDER BY b.djrq desc `
   var rtdata = await usequery(querysql)
   res_util(res, rtdata, true, "查询成功")
})

//查询已检验收料通知单列表
router.post('/sltzd_list_mobile_yj', async function (req, res) {
   let { keyword, startDataStrref, endDataStrref } = req.body
   let querysql = `select a.*,b.djrq,b.gysmc,b.djh from shipSRM_sltzd_mx a left JOIN  shipSRM_sltzd b ON a.mainid = b.id where a.sfllj is not null`
   if (keyword) {
      querysql += ` and (a.wlmc like '%${keyword}%' or a.ggxh like '%${keyword}%' or b.djh like '%${keyword}%')`
   }
   // if (startDataStrref && endDataStrref) {
   //    querysql = querysql.replace('TOP 1000', '')
   //    querysql += ` AND  create_time BETWEEN '${startDataStrref}' AND '${endDataStrref}' `;
   // } else if (startDataStrref) {
   //    querysql = querysql.replace('TOP 1000', '')
   //    querysql += ` AND  create_time > '${startDataStrref}' `;
   // } else if (endDataStrref) {
   //    querysql = querysql.replace('TOP 1000', '')
   //    querysql += ` AND  create_time < '${endDataStrref}' `;
   // }
   querysql += ` ORDER BY b.djrq desc `
   var rtdata = await usequery(querysql)
   res_util(res, rtdata, true, "查询成功")
})

//查询收料通知单列表通过id
router.post('/query_sltzd_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_sltzd where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_sltzd_mx where mainid ='${id}'`)
   res_util(res, { formdata: formdata[0], mxdata }, true, "查询成功")
})

//新增收料通知单
router.post('/add_sltzd', async function (req, res) {
   var db = new Msdb()
   let { formdata, mxdata, storage, sfxt } = req.body
   let xlm = await getxl_util_free("seq_sltzd")
   formdata.djh.value = "SLTZD" + xlm
   var insrtobj = await insert_table(formdata, "shipSRM_sltzd")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增收料通知单', '新增', 'shipSRM_sltzd')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      mxitem.sysl.value = mxitem.slsl.value
      var rtmxobj = await insert_table(mxitem, "shipSRM_sltzd_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增收料通知单明细', '新增', 'shipSRM_sltzd_mx')
      if (sfxt) {
         await db.update(`update shipSRM_cgdd_mx set xtzt='已下推' where id = '${mxitem.ydmxid.value}'`)
      }
   }

   await use_sltzd_to_cgdd()
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_sltzd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增收料通知单成功，单据号为{${formdata.djh.value}`)
})

//新增收料通知单并且免检下推采购入库单
router.post('/add_sltzd_mjxt', async function (req, res) {
   var db = new Msdb()
   let { formdata, mxdata, storage, sfxt } = req.body
   //新增收料通知单
   let xlm = await getxl_util_free("seq_sltzd")
   formdata.djh.value = "SLTZD" + xlm
   var insrtobj = await insert_table(formdata, "shipSRM_sltzd")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增收料通知单', '新增', 'shipSRM_sltzd')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   //免检下推购入库单
   let cgrkdid = await getxl_util()
   let cgrkdxlm = await getxl_util_free("seq_cgrkd")
   let djh = "CGRKD" + cgrkdxlm
   let sql = `insert into shipSRM_cgrk (id,create_time,create_by,djh,djlx,ddrk,rkrq,gysmc,gysid,cgy,cgyid,bgr,bgrid,ydbh,ydid,ydlx) 
      values ('${cgrkdid}',getdate(),'${storage.ygxm}','${djh}','现购入库单','是',getdate(),'${formdata.gysmc.value}','${formdata.gysbm.value}','${storage.ygxm}','${storage.ygbh}','${storage.ygxm}','${storage.ygbh}','${formdata.djh.value}','${mainid}','收料报检单')`
   await db.update(sql)
   await saveczlog_util(sql, storage, '', '免检新增采购入库单', '新增', 'shipSRM_cgrk')
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      mxitem.sysl.value = mxitem.slsl.value
      var rtmxobj = await insert_table(mxitem, "shipSRM_sltzd_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增收料通知单明细', '新增', 'shipSRM_sltzd_mx')
      if (sfxt) {
         await db.update(`update shipSRM_cgdd_mx set xtzt='已下推' where id = '${mxitem.ydmxid.value}'`)
         await db.update(`update shipSRM_sltzd_mx set xtzt='已下推',xtlx='免检下推' where id = '${rtmxobj.data.id.value}'`)
      }

      //免检下推购入库单明细
      let cgrkdmxid = await getxl_util()
      let sl= mxitem.slsl.value-mxitem.bls.value
      let mxsql=`insert into shipSRM_cgrk_detail (id,mainid,create_time,create_by,ph,ck,ckid,ydid,ydbh,ydmxid,ydlx,jdbm,wlmc,wlbm,ggxh,dw,bbh,slsl,sl) 
      values ('${cgrkdmxid}','${cgrkdid}',getdate(),'${storage.ygxm}','000','原料库','100000165','${mainid}','${formdata.djh.value}','${rtmxobj.data.id.value}','收料报检单','${mxitem.jdbm.value}','${mxitem.wlmc.value}','${mxitem.wlbm.value}',
      '${mxitem.ggxh.value}','${mxitem.dw.value}','${mxitem.bbh.value}','${mxitem.slsl.value}','${sl}')`
      await db.update(mxsql)
      await saveczlog_util(mxsql, storage, '', '免检新增采购入库单明细', '新增', 'shipSRM_cgrk_detail')
   }
   await use_sltzd_to_cgdd()
   await use_cgrkd_to_cgdd()
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_sltzd_mx where mainid ='${formdata.id.value}'`)
   var rtmxdata2 = await usequery(`select top 1 *  from shipSRM_cgrk_detail where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata, mxdata2: rtmxdata2 }, true, `新增收料通知单成功，单据号为{${formdata.djh.value}/n新增采购入库单成功，单据号为{${djh}}`)
})

//更新收料通知单
router.post('/update_sltzd', async function (req, res) {
   let db = new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_sltzd")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改收料通知单', '修改', 'shipSRM_sltzd')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_sltzd_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_sltzd_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_sltzd_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_sltzd_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   await use_sltzd_to_cgdd()

   var rtmxdata = await usequery(`select *  from shipSRM_sltzd_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新收料通知单成功")
})

//  删除收料通知单
router.post('/delete_sltzd', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db = new Msdb()
   var ifcz = await usequery(`select * from shipSRM_cgrk_detail where ydid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   if (ifcz.length > 0) {
      res_util(res, {}, false, "删除失败，存在关联数据")
      return
   }
   const delsql = `delete from shipSRM_sltzd where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_sltzd_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除收料通知单', '删除', 'shipSRM_sltzd')
   await use_sltzd_to_cgdd()
   res_util(res, {}, true, "删除成功")
})

//查询收料通知单通过单据编号
router.post('/querySltzdByDjbh', async function (req, res) {
   var rtdata = await usequery(`
      SELECT a.id,a.create_by create_byq,a.gysmc gysq,a.cgymc,a.djrq,a.djh ydh,a.gysbm,a.cgybm,
       b.wlmc, b.wlbm, b.ggxh, b.dw,b.cgsl,b.slsl,b.rksl,b.sysl,b.bbh,b.xtzt,b.sfllj,b.id AS mxid
      FROM shipSRM_sltzd a
      INNER JOIN shipSRM_sltzd_mx b ON b.mainid = a.id
	   where  a.djh like '%${req.body.keyword}%' or b.wlmc like '%${req.body.keyword}%' or b.ggxh like '%${req.body.keyword}%'  
	   order by a.create_time desc `);

   res_util(res, rtdata, true, "查询成功")
});

//更新收料通知单检验状态
router.post('/updateSltzdJyzt', async function (req, res) {
   let { storage } = req.body
   let sql = `update shipSRM_sltzd_mx set sfllj='${req.body.jyjg}',jys='${req.body.jys}',bz='${req.body.bz}',bls='${req.body.bls}',
       jyr='${storage.ygxm}',jysj=getdate() where id='${req.body.mxid}'`;
   var rtdata = await usequery(sql);
   await saveczlog_util(sql, storage, '', '更新收料通知单检验状态', '修改', 'shipSRM_sltzd_mx')
   res_util(res, rtdata, true, "更新成功")
});

//查询采购退货单列表
router.post('/cgckd_list', async function (req, res) {
   let { startDataStrref, endDataStrref, type } = req.body
   var querysql = ``
   type == 'mx' ? querysql = `
      SELECT top 1000 a.ydbh,a.ydid,a.id,a.create_by create_byq,a.gysmc gysq,a.cgy cgyq,a.thrq,a.djh ydh,a.bz,
   CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
       THEN a.djh ELSE NULL END AS djh,
   CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
       THEN a.create_by ELSE NULL END AS create_by,
   CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
       THEN a.create_time ELSE NULL END AS create_time,
   CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
       THEN a.gysmc ELSE NULL END AS gysmc,
    b.wlmc, b.wlbm, b.ggxh, b.sl,b.bbh,b.id AS mxid,b.jdbm
   FROM shipSRM_cgck a
   INNER JOIN shipSRM_cgck_mx b ON b.mainid = a.id
   where 1=1

 `: querysql = `select top 1000 * from shipSRM_cgck where 1=1`
   if (startDataStrref && endDataStrref) {
      querysql = querysql.replace('top 1000', '')
      querysql += ` AND a.create_time BETWEEN '${startDataStrref}' AND '${endDataStrref} 23:59:59' `;
   } else if (startDataStrref) {
      querysql = querysql.replace('top 1000', '')
      querysql += ` AND a.create_time > '${startDataStrref}' `;
   } else if (endDataStrref) {
      querysql = querysql.replace('top 1000', '')
      querysql += ` AND a.create_time < '${endDataStrref} 23:59:59' `;
   }

   if (type === "mx") {
      querysql += ` ORDER BY a.create_time DESC, b.id `
   } else {
      querysql += ` ORDER BY create_time desc `
   }
   var rtdata = await usequery(querysql)
   res_util(res, rtdata, true, "查询成功")
})

//查询采购退货单列表通过id
router.post('/query_cgckd_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_cgck where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_cgck_mx where mainid ='${id}'`)
   res_util(res, { formdata: formdata[0], mxdata }, true, "查询成功")
})

//新增采购退货单
router.post('/add_cgckd', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util_free("seq_cgthd")
   formdata.djh.value = "CGTHD" + xlm
   var insrtobj = await insert_table(formdata, "shipSRM_cgck")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增采购退货单', '新增', 'shipSRM_cgck')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_cgck_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增采购退货单明细', '新增', 'shipSRM_cgck_mx')
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_cgck_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增采购退货单成功，单据号为${formdata.djh.value}`)
})

//更新采购退货单
router.post('/update_cgckd', async function (req, res) {
   let db = new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_cgck")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改采购退货单', '修改', 'shipSRM_cgck')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_cgck_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_cgck_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_cgck_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_cgck_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_cgck_mx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新采购退货单成功")
})

//  删除采购退货单
router.post('/delete_cgckd', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db = new Msdb()
   const delsql = `delete from shipSRM_cgck where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_cgck_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除采购退货单', '删除', 'shipSRM_cgck')
   res_util(res, {}, true, "删除成功")
})

//查询采购合同列表
router.post('/cght_list', async function (req, res) {
   var rtdata = await usequery(`select * from shipSRM_cght order by id desc`)
   res_util(res, rtdata, true, "查询成功")
})

//查询采购合同列表通过id
router.post('/query_cght_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_cght where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_cght_mx where mainid ='${id}'`)
   var fkmxdata = await usequery(`select *  from shipSRM_cght_fkmx where mainid ='${id}'`)
   res_util(res, { formdata: formdata[0], mxdata, fkmxdata }, true, "查询成功")
})

//新增采购合同
router.post('/add_cght', async function (req, res) {
   let { formdata, mxdata, fkmxdata, storage } = req.body

   var insrtobj = await insert_table(formdata, "shipSRM_cght")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增采购合同', '新增', 'shipSRM_cght')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_cght_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增采购合同明细', '新增', 'shipSRM_cght_mx')
   }
   for (var mxkey in fkmxdata) {
      let mxitem = fkmxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_cght_fkmx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增采购合同参数明细', '新增', 'shipSRM_cght_mx')
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_cght_mx where mainid ='${formdata.id.value}'`)
   var rtfkmxdata = await usequery(`select top 1 *  from shipSRM_cght_fkmx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata, fkmxdata: rtfkmxdata }, true, `新增采购合同成功,采购合同单据号${formdata.djh.value}`)
})

//更新采购合同
router.post('/update_cght', async function (req, res) {
   let db = new Msdb()
   let { formdata, mxdata, fkmxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_cght")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改采购合同', '修改', 'shipSRM_cght')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_cght_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_cght_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_cght_mx")
      }
   }
   for (var mxkey in fkmxdata) {
      let mxitem = fkmxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_cght_fkmx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_cght_fkmx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_cght_fkmx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_cght_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_cght_mx where mainid ='${formdata.id.value}'`)
   await db.update(`delete from shipSRM_cght_fkmx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtfkmxdata = await usequery(`select *  from shipSRM_cght_fkmx where mainid ='${formdata.id.value}'`)
   res_util(res, { formdata: formdata, mxdata: rtmxdata, fkmxdata: rtfkmxdata }, true, "更新采购合同成功")
})

//  新增请购单移动
router.post('/queryQgdByJybh', async function (req, res) {
   let { keyword} = req.body
   var rtdata = await usequery(`select * from shipSRM_qgd where djh like '%${keyword}%' or CONVERT(varchar(100), djrq, 23) like '%${keyword}%'`)
   res_util(res, rtdata, true, "查询成功")
})


//  新增请购单移动
router.post('/add_qgd_mx', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let db = new Msdb()
   let id = await getxl_util("")
   let xlm = await getxl_util_free("seq_qgd")
   formdata.djh.value = "QGD" + xlm
   await db.update(`insert into shipSRM_qgd(ID, create_by, create_time, djh, djrq,bz) 
				values('${id}','${storage.ygxm}',getdate(),'${formdata.djh.value}',getdate(),'${formdata.bz.value}')`)
   var mainid = id;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      let mid = await getxl_util()
      await db.update(`insert into shipSRM_qgd_mx(ID, create_by, create_time, update_by, update_time,
         mainid, wlmc, wlbm, ywlbm, ggxh, dw, bbh, qgsl, bzsm,wltp)
               values('${mid}','${storage.ygxm}',getdate(),
               '${storage.ygxm}',getdate(),'${mainid}',
               '${mxitem.wlmc}','${mxitem.wlbm}','${mxitem.ywlbm}',
               '${mxitem.ggxh}','${mxitem.dw}','${mxitem.bbh}',
               '${mxitem.qgsl}','${mxitem.bzsm}','${mxitem.wltp?mxitem.wltp:""}'
            )`)
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_qgd_mx where mainid ='${formdata.id.value}'`)
   await use_qdgmx_qgd()
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增请购单成功，单据号为${formdata.djh.value}`)
})

//  更新请购单
router.post('/update_qgd_mx', async function (req, res) {
   let { mainid, mxdata, delidarr, storage,formdata } = req.body
   var db = new Msdb()
   await db.update(`update shipSRM_qgd set bz='${formdata.bz.value}' where id='${mainid}'`)
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_qgd_mx where id='${mxitem.id}'`)
      if (!ifcz) {
         let mid = await getxl_util()
         await db.update(`insert into shipSRM_qgd_mx(ID, create_by, create_time, update_by, update_time,
            mainid, wlmc, wlbm, ywlbm, ggxh, dw, bbh, qgsl, bzsm,wltp)
                  values('${mid}','${storage.ygxm}',getdate(),
                  '${storage.ygxm}',getdate(), '${mainid}',
                  '${mxitem.wlmc}','${mxitem.wlbm}','${mxitem.ywlbm}',
                  '${mxitem.ggxh}','${mxitem.dw}','${mxitem.bbh}',
                  '${mxitem.qgsl}','${mxitem.bzsm}','${mxitem.wltp?mxitem.wltp:""}'
               )`)
      } else {
         let sql = `update shipSRM_qgd_mx set wlmc='${mxitem.wlmc}',wlbm='${mxitem.wlbm}',ywlbm='${mxitem.ywlbm}',update_time=getdate(),
         update_by='${storage.ygxm}',
         ggxh='${mxitem.ggxh}',dw='${mxitem.dw}',bbh='${mxitem.bbh}',qgsl='${mxitem.qgsl}',bzsm='${mxitem.bzsm}',wltp='${mxitem.wltp}'
         where id='${mxitem.id}'`
         await db.update(sql)
      }
   }
   await db.update(`delete from shipSRM_qgd_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   await use_qdgmx_qgd()
   res_util(res, {}, true, "更新成功")
})

//  删除采购合同
router.post('/delete_cght', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db = new Msdb()
   const delsql = `delete from shipSRM_cght where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_cght_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await db.update(`delete from shipSRM_cght_fkmx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除采购合同', '删除', 'shipSRM_cght')
   res_util(res, {}, true, "删除成功")
})

//查询请购单列表
router.post('/qgd_list_mobile', async function (req, res) {
   let { startDataStrref, endDataStrref } = req.body
   var querysql = `select top 1000* from shipSRM_qgd where 1=1`
   if (startDataStrref && endDataStrref) {
      querysql.replace("top 1000", "")
      querysql += ` AND djrq BETWEEN '${startDataStrref}' AND ${endDataStrref} 23:59:59' `;
   } else if (startDataStrref) {
      querysql.replace("top 1000", "")
      querysql += ` AND djrq > '${startDataStrref}' `;
   } else if (endDataStrref) {
      querysql.replace("top 1000", "")
      querysql += ` AND djrq < ${endDataStrref} 23:59:59' `;
   }

   querysql += ` ORDER BY djrq desc `
   var rtdata = await usequery(querysql)
   res_util(res, rtdata, true, "查询成功")
})


//查询请购单列表
router.post('/qgd_list', async function (req, res) {
//    CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
//    THEN a.djrq ELSE NULL END AS djrq,
//   CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
//    THEN a.djh ELSE NULL END AS djh,
//   CASE WHEN ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY b.id) = 1 
//    THEN a.create_by ELSE NULL END AS zdr
   let { startDataStrref, endDataStrref } = req.body
   var querysql = `select top 1000 a.id,a.djh as djbh,a.create_by,b.wlmc,b.wlbm,b.ggxh,b.dw,b.bbh,b.qgsl,b.bzsm,b.ywlbm,a.djrq
         from shipSRM_qgd a left join shipSRM_qgd_mx b on a.id=b.mainid where 1=1`
   if (startDataStrref && endDataStrref) {
      querysql.replace("top 1000", "")
      querysql += ` AND a.djrq BETWEEN '${startDataStrref}' AND '${endDataStrref} 23:59:59' `;
   } else if (startDataStrref) {
      querysql.replace("top 1000", "")
      querysql += ` AND a.djrq > '${startDataStrref}' `;
   } else if (endDataStrref) {
      querysql.replace("top 1000", "")
      querysql += ` AND a.djrq < '${endDataStrref} 23:59:59' `;
   }

   querysql += ` ORDER BY a.djrq desc `
   var rtdata = await usequery(querysql)
   res_util(res, rtdata, true, "查询成功")
})

//查询请购单列表通过id
router.post('/query_qgd_byid', async function (req, res) {
   var id = req.body.id
   var formdata = await usequery(`select *  from shipSRM_qgd where id ='${id}'`)
   var mxdata = await usequery(`select *  from shipSRM_qgd_mx where mainid ='${id}'`)
   res_util(res, { formdata: formdata[0], mxdata }, true, "查询成功")
})

//新增请购单
router.post('/add_qgd', async function (req, res) {
   let { formdata, mxdata, storage } = req.body
   let xlm = await getxl_util_free("seq_qgd")
   formdata.djh.value = "QGD" + xlm
   var insrtobj = await insert_table(formdata, "shipSRM_qgd")
   await saveczlog_util(insrtobj.insertsql, storage, '', '新增请购单', '新增', 'shipSRM_qgd')
   formdata = insrtobj.data;
   var mainid = insrtobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = await insert_table(mxitem, "shipSRM_qgd_mx")
      await saveczlog_util(rtmxobj.insertsql, storage, '', '新增请购单明细', '新增', 'shipSRM_qgd_mx')
   }
   var rtmxdata = await usequery(`select top 1 *  from shipSRM_qgd_mx where mainid ='${formdata.id.value}'`)
   await use_qdgmx_qgd()
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, `新增请购单成功，单据号为${formdata.djh.value}`)
})

//更新请购单
router.post('/update_qgd', async function (req, res) {
   let db = new Msdb()
   let { formdata, mxdata, storage, delidarr } = req.body
   let updatobj = await update_table_byid(formdata, "shipSRM_qgd")
   await saveczlog_util(updatobj.updatesql, storage, '', '修改请购单', '修改', 'shipSRM_qgd')
   formdata = updatobj.data;
   var mainid = updatobj.data.id.value;
   for (var mxkey in mxdata) {
      let mxitem = mxdata[mxkey];
      var ifcz = await checkbysql(`select * from shipSRM_qgd_mx where id='${mxitem.id.value}'`)
      mxitem.mainid = { dataIndex: "mainid", value: mainid, ifdb: true }
      var rtmxobj = {}
      if (!ifcz) {
         rtmxobj = await insert_table(mxitem, "shipSRM_qgd_mx")
      } else {
         rtmxobj = await update_table_byid(mxitem, "shipSRM_qgd_mx")
      }
   }

   //删除delidarr
   await db.update(`delete from shipSRM_qgd_mx where id in (${delidarr.map(item => `'${item}'`).join(',')}) `)
   var rtmxdata = await usequery(`select *  from shipSRM_qgd_mx where mainid ='${formdata.id.value}'`)
   await use_qdgmx_qgd()
   res_util(res, { formdata: formdata, mxdata: rtmxdata }, true, "更新请购单成功")
})

//  删除请购单
router.post('/delete_qgd', async function (req, res) {
   let { selectedRows, storage } = req.body
   var db = new Msdb()
   const delsql = `delete from shipSRM_qgd where id in (${selectedRows.map(item => `'${item.id}'`).join(',')})`
   await db.update(delsql)
   await db.update(`delete from shipSRM_qgd_mx where mainid in (${selectedRows.map(item => `'${item.id}'`).join(',')})`)
   await saveczlog_util(delsql, storage, '', '删除请购单', '删除', 'shipSRM_qgd')
   await use_qdgmx_qgd()
   res_util(res, {}, true, "删除成功")
})

module.exports = router; 